
package com.ce.cases.hear.logic.search;

import com.ce.cases.hear.view.CaseSourceHearView;
import com.ce.framework.datamaintain.action.SearchAction;
import com.ce.framework.datamaintain.logic.ISQLMapper;
import com.ce.framework.datamaintain.logic.SQLSearchCondition;
import com.ce.framework.exception.AppException;
import com.ce.framework.util.DateUtil;
import com.ce.framework.util.StringUtil;
import com.ce.sys.login.model.User;
import com.ce.sys.org.dao.po.SysVOrg;
import com.ce.util.OrgUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

/**
* 嫦娥软件有限公司 版权所有 2013
* 创建人：　andy
* 创建日期：2014-04-08
* 创建时间：15:05:29
* 功能描述：案源审理 [CASE_SOURCE_HEAR]条件过滤类
* ==============================================
* 修改历史
* 修改人		修改时间		修改原因
*
* ==============================================
*/
public class CaseSourceHearSearchCondition2  extends SQLSearchCondition	implements ISQLMapper
{
	private String taxpayerNum;
    private String involvedEnt;
    private String caseNum;
    private String recBeginDate;
    private String recEndDate;
    private String caseFrom;
    private String illegalType;
    private String inspectDept;
    private String collectDept;
    
    private String curOrgId;
    private String curPersonId;
    private String curOrgLevel;
    private String curOrgType;   
    private String curRootOrgId;
    private String curPersonPosition;//当前人职位
    @Override
	public String getExcelName() {
		return "稽查案件审理工作台账_"+DateUtil.format(new Date(),"yyyyMMddHHmm");
	}

	@Override
	public void initCondition(SearchAction action, boolean search)
			throws AppException {
		User user=User.getUser();
		SysVOrg curOrg=OrgUtil.getOrg(User.getUserOrgId());
		this.curOrgId=curOrg.getVcId();
		this.curOrgLevel=curOrg.getVcOrgLevel();
		this.curPersonId=User.getUserId();
		this.curOrgType=curOrg.getVcType();
		curRootOrgId=user.getRootOrgId();
		curPersonPosition=user.getVcPosition();
	}
    
	public String getSearchSql() throws AppException {
		StringBuffer sql = new StringBuffer("select t1.vc_id as vcId,t1.case_number caseNumber,t1.is_secret,t1.involved_enterprice,t1.illegal_type_name,")
          .append(" t2.*")
          .append(",draw.register_Date drawRegisterDate,work.VC_STATUS,work.VC_HANDLE_ORGNAME")
          .append(" from case_source t1,case_source_hear t2,CASE_SOURCE_COLLEGIAL_DRAW draw,T_WORKFLOW_WORK work ")
          .append(" where t1.vc_del$flag=0 and t1.vc_id=work.VC_RECORD_ID and t1.vc_id=t2.case_id(+) and t1.vc_id=draw.case_id(+)  and t1.STATE=3 and to_number(work.VC_STATUS)>=3 ");
		
	        //采用变量绑定的方式提高查询效率
			if(StringUtil.isNotBlank(taxpayerNum)){
				sql.append(" and t1.taxpayer_number like ${taxpayerNum}||'%'");
			}
			if(StringUtil.isNotBlank(involvedEnt)){
				sql.append(" and t1.involved_enterprice like '%'||${involvedEnt}||'%'");
			}
			if(StringUtil.isNotBlank(caseNum)){
				sql.append(" and t1.case_number=${caseNum}");
			}
			/*
			 * 2015-06-09 by 刘渝洪，开始 
			 * 修改搜索处理
			 * 默认初始列表时，或搜索日期条件没值时，搜索出的结果为：全部未结案的数据，以及本年度的已结案数据
			 * 设置搜索日期条件时，搜索出的结果为：满足日期条件的全部数据
			 * 原代码：
			 * sql.append(this.getDateCondition("receive_date", recBeginDate, recEndDate));
			 * 修改后代码：
			 */
			if (StringUtil.isNotBlank(recBeginDate)
					|| StringUtil.isNotBlank(recEndDate)) {
				sql.append(this.getDateCondition("receive_date", recBeginDate, recEndDate));
			} else {
				sql.append(" and (to_number(work.vc_status)<>9 or (to_number(work.vc_status)=9 ");
				String dateBeginOfThisYear = DateUtil.getYear() + "-01-01 00:00:00";
				String dateEndOfThisYear = DateUtil.getYear() + "-12-31 23:59:59";
				sql.append(" and ").append("receive_date").append(">=to_date('")
						.append(dateBeginOfThisYear)
						.append("','yyyy-mm-dd hh24:mi:ss') ");
				sql.append(" and ").append("receive_date").append("<=to_date('")
						.append(dateEndOfThisYear)
						.append("','yyyy-mm-dd hh24:mi:ss') ");
				sql.append("))");
			}
			/* 2015-06-09 by 刘渝洪，结束 */
			if(StringUtil.isNotBlank(caseFrom)){
				sql.append(" and t1.case_from_num=${caseFrom}");
			}
			if(StringUtil.isNotBlank(illegalType)){
				sql.append(" and t1.illegal_type_hear_num=${illegalType}");
			}
			if(StringUtil.isNotBlank(inspectDept)){
				sql.append(" and t1.inspection_dept_id=${inspectDept}");
			}
			if(StringUtil.isNotBlank(collectDept)){
				sql.append(" and t1.collection_dept_id=${collectDept}");
			}
		return sql.toString();
	}
	
	public Object fromSQLQuery(ResultSet rs, int num) {
		CaseSourceHearView view = new CaseSourceHearView();
		try {
			view.setVcId(rs.getString("vcId"));
			view.setIsSecret(rs.getInt("is_secret"));
			view.setCaseNumber(rs.getString("caseNumber"));
			view.setEnterprice(rs.getString("involved_enterprice"));
			view.setHearDate(rs.getDate("hear_date"));
			view.setHearPerson(rs.getString("hear_person"));
			if(rs.getString("is_retrial")!=null)
				view.setIsRetrial(rs.getInt("is_retrial"));
			if(rs.getString("is_collegiate")!=null)
			   view.setIsCollegiate(rs.getInt("is_collegiate"));
			view.setIllegalTypeName(rs.getString("illegal_type_name"));
			view.setTotalCount(rs.getInt("total_count"));//总份数
			view.setTotalMoney(rs.getDouble("total_money"));//总金额
			if(rs.getString("is_hearing")!=null)
			   view.setIsHearing(rs.getInt("is_hearing"));//是否听证
			if(rs.getString("is_reconsideration")!=null)
			   view.setIsReconsideration(rs.getInt("is_reconsideration"));//是否行政复议
			if(rs.getString("is_procedure")!=null)
			   view.setIsProcedure(rs.getInt("is_procedure"));//是否行政诉讼
			if(rs.getString("is_compensation")!=null)
			   view.setIsCompensation(rs.getInt("is_compensation"));//是否行政赔偿
			if(rs.getString("PUNISHMENT_COUNT")!=null)
			  view.setPunishmentCount(rs.getInt("PUNISHMENT_COUNT"));//近五年被处罚次数
			
			if(rs.getString("TRANSPORT_COUNT")!=null)
				  view.setTransportCount(rs.getInt("TRANSPORT_COUNT"));//交通
			if(rs.getString("TRANSPORT_MONEY")!=null)
				  view.setTransportMoney(rs.getDouble("TRANSPORT_MONEY"));//交通
			if(rs.getString("CONSTRUCTION_COUNT")!=null)
				  view.setConstructionCount(rs.getInt("CONSTRUCTION_COUNT"));//建筑安装
			if(rs.getString("CONSTRUCTION_MONEY")!=null)
				  view.setConstructionMoney(rs.getDouble("CONSTRUCTION_MONEY"));//建筑安装
			if(rs.getString("COMMERCIAL_COUNT")!=null)
				  view.setCommercialCount(rs.getInt("COMMERCIAL_COUNT"));//近五年被处罚次数
			
			
			view.setTreatmentResultName(rs.getString("TREATMENT_RESULT_NAME"));	
			
			
			
			view.setHearRegisterPerson(rs.getString("HEAR_REGISTER_PERSON"));
			view.setHearRegisterDeptid(rs.getString("HEAR_REGISTER_DEPTID"));
			view.setHearRegisterDate(rs.getDate("HEAR_REGISTER_DATE"));
			view.setLocalAddedTax(rs.getDouble("LOCAL_ADDED_TAX"));
			view.setLocalCostTax(rs.getDouble("LOCAL_COST_TAX"));
			view.setOperationTax(rs.getDouble("OPERATION_TAX"));
			view.setConstructionTax(rs.getDouble("CONSTRUCTION_TAX"));
			view.setEntIncomeTax(rs.getDouble("ENT_INCOME_TAX"));
			view.setPersonalIncomeTax(rs.getDouble("PERSONAL_INCOME_TAX"));
			view.setResourceTax(rs.getDouble("RESOURCE_TAX"));
			view.setEducationTax(rs.getDouble("EDUCATION_TAX"));
			view.setIllegalMoney(rs.getDouble("ILLEGAL_MONEY"));
			view.setAdjustFillTax(rs.getDouble("ADJUST_FILL_TAX"));
			view.setNoFillTax(rs.getDouble("NO_FILL_TAX"));
			view.setNoFillbackTax(rs.getDouble("NO_FILLBACK_TAX"));
			view.setAdjustIncome(rs.getDouble("ADJUST_INCOME"));
			view.setDeductPaidTax(rs.getDouble("DEDUCT_PAID_TAX"));
			view.setRecoveredRefundedTax(rs.getDouble("RECOVERED_REFUNDED_TAX"));
			view.setFillRegisterPerson(rs.getString("FILL_REGISTER_PERSON"));
			view.setFillRegisterDate(rs.getDate("FILL_REGISTER_DATE"));
			view.setFillRegisterDeptid(rs.getString("FILL_REGISTER_DEPTID"));
			view.setCommercialCount(rs.getInt("COMMERCIAL_COUNT"));
			view.setCommercialMoney(rs.getDouble("COMMERCIAL_MONEY"));
			view.setCateringCount(rs.getInt("CATERING_COUNT"));
			view.setCateringMoney(rs.getDouble("CATERING_MONEY"));
			view.setAddedTaxCount(rs.getInt("ADDED_TAX_COUNT"));
			view.setAddedTaxMoney(rs.getDouble("ADDED_TAX_MONEY"));
			view.setOtherCount(rs.getInt("OTHER_COUNT"));
			view.setOtherMoney(rs.getDouble("OTHER_MONEY"));
			view.setTotalCount(rs.getInt("TOTAL_COUNT"));
			view.setTotalMoney(rs.getDouble("TOTAL_MONEY"));
			view.setIllegalRegisterPerson(rs.getString("ILLEGAL_REGISTER_PERSON"));
			view.setIllegalRegisterDeptid(rs.getString("ILLEGAL_REGISTER_DEPTID"));
			view.setIllegalRegisterDate(rs.getDate("ILLEGAL_REGISTER_DATE"));
			view.setIsReconsideration(rs.getInt("IS_RECONSIDERATION"));
			view.setIsProcedure(rs.getInt("IS_PROCEDURE"));
			view.setIsCompensation(rs.getInt("IS_COMPENSATION"));
			view.setCompensationMoney(rs.getInt("COMPENSATION_MONEY"));
			view.setTrackRegisterPerson(rs.getString("TRACK_REGISTER_PERSON"));
			view.setTrackRegisterDeptid(rs.getString("TRACK_REGISTER_DEPTID"));
			view.setTrackRegisterDate(rs.getDate("TRACK_REGISTER_DATE"));
			view.setTransferNumber(rs.getInt("TRANSFER_NUMBER"));
			view.setTreatmentNumber(rs.getInt("TREATMENT_NUMBER"));
			view.setTreatmentResultNum(rs.getString("TREATMENT_RESULT_NUM"));
			view.setJudicialRegisterPerson(rs.getString("JUDICIAL_REGISTER_PERSON"));
			view.setJudicialRegisterDeptid(rs.getString("JUDICIAL_REGISTER_DEPTID"));
			view.setJudicialRegisterDate(rs.getDate("JUDICIAL_REGISTER_DATE"));
			view.setFaKuanMoney(rs.getDouble("FAKUAN_MONEY"));
			
			
			
			view.setState(rs.getInt("VC_STATUS"));
			
			view.setDrawRegisterDate(rs.getDate("drawRegisterDate"));

		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return view;
	}
	
	public ISQLMapper getSQLMapper() {
		
		return this;
	}

	public String getTaxpayerNum() {
		return taxpayerNum;
	}

	public void setTaxpayerNum(String taxpayerNum) {
		this.taxpayerNum = taxpayerNum;
	}

	public String getInvolvedEnt() {
		return involvedEnt;
	}

	public void setInvolvedEnt(String involvedEnt) {
		this.involvedEnt = involvedEnt;
	}

	public String getCaseNum() {
		return caseNum;
	}

	public void setCaseNum(String caseNum) {
		this.caseNum = caseNum;
	}

	public String getRecBeginDate() {
		return recBeginDate;
	}

	public void setRecBeginDate(String recBeginDate) {
		this.recBeginDate = recBeginDate;
	}

	public String getRecEndDate() {
		return recEndDate;
	}

	public void setRecEndDate(String recEndDate) {
		this.recEndDate = recEndDate;
	}

	public String getCaseFrom() {
		return caseFrom;
	}

	public void setCaseFrom(String caseFrom) {
		this.caseFrom = caseFrom;
	}

	public String getIllegalType() {
		return illegalType;
	}

	public void setIllegalType(String illegalType) {
		this.illegalType = illegalType;
	}

	public String getInspectDept() {
		return inspectDept;
	}

	public void setInspectDept(String inspectDept) {
		this.inspectDept = inspectDept;
	}

	public String getCollectDept() {
		return collectDept;
	}

	public void setCollectDept(String collectDept) {
		this.collectDept = collectDept;
	}

	public String getCurOrgId() {
		return curOrgId;
	}

	public void setCurOrgId(String curOrgId) {
		this.curOrgId = curOrgId;
	}

	public String getCurPersonId() {
		return curPersonId;
	}

	public void setCurPersonId(String curPersonId) {
		this.curPersonId = curPersonId;
	}

	public String getCurOrgLevel() {
		return curOrgLevel;
	}

	public void setCurOrgLevel(String curOrgLevel) {
		this.curOrgLevel = curOrgLevel;
	}

	public String getCurOrgType() {
		return curOrgType;
	}

	public void setCurOrgType(String curOrgType) {
		this.curOrgType = curOrgType;
	}
	
	

}

